var express = require('express');
var router = express.Router();


const DB = require("../utils/db")
const { api_url } = require("../utils/config")


// 地区
router.get("/areas", async (req, res, next) => {

    let sql = `select area_name  from  e_area`

    let [err, result] = await DB(sql)

    if (err) {
        next("地区请求失败")
        return
    }

    // 处理数据
    // let newresult=[]
    // result.forEach(item => {
    //     newresult.push(item.area_name);
    // });

    res.send({
        status: 200,
        msg: "地区请求成功",
        result
    })


})


// 年级
router.get("/grades", async (req, res, next) => {
    let sql = `select grade_name  from  e_grade`

    let [err, result] = await DB(sql)
    if (err) {
        next("年级请求失败")
        return
    }

    res.send({
        status: 200,
        msg: "年级请求成功",
        result
    })
})

// 科目
router.get("/subjects", async (req, res, next) => {
    let sql = `select subject_name  from  e_subject`

    let [err, result] = await DB(sql)
    if (err) {
        next("科目请求失败")
        return
    }

    res.send({
        status: 200,
        msg: "科目请求成功",
        result
    })
})

// 考试类型
router.get("/cates", async (req, res, next) => {
    let sql = `select category_name  from  e_category`

    let [err, result] = await DB(sql)
    if (err) {
        next("考试类型请求失败")
        return
    }

    res.send({
        status: 200,
        msg: "考试类型请求成功",
        result
    })
})



// 列表渲染
/* router.get("/courselist",async (req,res,next)=>{
    // 请求课程列表
    let sql=`select id,
                    cid,
                    title,
                    price,
                    CONCAT("${api_url}","/uploads/","course/",image_src)  as image_src, 
                    area_name,
                    subject_name
                from e_course 
                where status!=4`
    let [err,result]=await DB(sql)
    console.log(result.length);
    res.send(result);
}) */


// 页码数据和点击分页
/*router.get("/courselist",async (req,res,next)=>{
    // 参数1：size 一页展示多少条数 默认值为6
    // 参数2：page 当前页码  默认值为1
    let {size=6,page=1}=req.query


    // limit 开始获取数据的下标,一次获取多少条数据
    // let index=page*10-10=(page-1)*size
    // page   index 
    //  1      0
    //  2      10
    //  3      20
    //  4      30  
    // page   index 
    //  1      0
    //  2      6
    //  3      12
    //  4      18  
    // 计算 下开始下标
    let index=(page-1)*size


    // 请求课程列表

    //let sql=`select id,
    //                cid,
    //                title,
    //                price,
    //                CONCAT("${api_url}","/uploads/","course/",image_src)  as image_src, 
    //                area_name,
    //                subject_name
    //           from e_course 
    //           where status!=4
    //           limit ${size}` 

    let sql=`select id,
            cid,
            title,
            price,
            CONCAT("${api_url}","/uploads/","course/",image_src)  as image_src, 
            area_name,
            subject_name
        from e_course 
        where status!=4
        limit ${index},${size}`

    let [err,result]=await DB(sql)
    // console.log(result.length);//当前页的个数
    if(err){
        next("课程列表请求失败")
        return
    }


    // 总页码：总条数/size
    
    let sql1=`select id,
            cid,
            title,
            price,
            CONCAT("${api_url}","/uploads/","course/",image_src)  as image_src, 
            area_name,
            subject_name
        from e_course 
        where status!=4`

    let [err1,result1]=await DB(sql1)
    // console.log(result1.length);//总条数

    if(err1){
        next("课程列表失败")
        return
    }

    let sizes=result1.length;
    let pages=Math.ceil(sizes/size)
    // console.log(pages);//总页码
    
    res.send({
        status:200,
        msg:"课程列表请求成功",
        result:{
            data:result,
            page:Number(page),
            pages,
            sizes
        }
        
    });
}) 
*/




// 筛选
router.get("/courselist", async (req, res, next) => {

    // 每一个都不是必传的
    let { size = 6, page = 1, grade_name, category_name, subject_name, area_name } = req.query

    // 前端可能会传这个几个参数，但是传不传不知道，传几个不知道，先传谁不知道，什么时候传不知道（不管传不传，我们都需要预设接参）

    // and grade_name="年级"
    // and category_name="期中"
    // and subject_name="一年级"
    // and area_name="北京"

    // 假设传递了其中某项

    // 计算 下开始下标
    let index = (page - 1) * size
    // 查询当前页的数据
    let sql = `select   id,
                        cid,
                        title,
                        price,
                        CONCAT("${api_url}","/uploads/","course/",image_src)  as image_src, 
                        area_name,
                        subject_name
                    from e_course 
                    where status!=4

        ${grade_name ? "and grade_name=" + grade_name : ""}
        ${category_name ? "and category_name=" + category_name : ""}
        ${subject_name ? "and subject_name=" + subject_name : ""}
        ${area_name ? "and area_name=" + area_name : ""}
        
        limit ${index},${size}`


    let [err, result] = await DB(sql)

    if (err) {
        next("课程列表请求失败")
        return
    }


    // 总页码：总条数/size
    let sql1 = `select id,
            cid,
            title,
            price,
            CONCAT("${api_url}","/uploads/","course/",image_src)  as image_src, 
            area_name,
            subject_name
        from e_course 
        where status!=4
             
        ${grade_name ? "and grade_name=" + grade_name : ""}
        ${category_name ? "and category_name=" + category_name : ""}
        ${subject_name ? "and subject_name=" + subject_name : ""}
        ${area_name ? "and area_name=" + area_name : ""}
        
        `

    let [err1, result1] = await DB(sql1)

    console.log(err1);
    if (err1) {
        next("课程列表失败")
        return
    }

    //总条数
    let sizes = result1.length;
    //总页码
    let pages = Math.ceil(sizes / size)


    res.send({
        status: 200,
        msg: "课程列表请求成功",
        result: {
            data: result,
            page: Number(page),
            pages,
            sizes
        }

    });
})




module.exports = router;

